<?php
    require_once ("Includes/simplecms-config.php");
    require_once  ("Functions/scanresults.php");
    require_once ("Includes/error.php");

    function prep_DB_content ()
    {
        global $databaseConnection;
        $admin_role_id = 1;

        create_tables($databaseConnection);
        create_roles($databaseConnection, $admin_role_id);
        create_admin($databaseConnection, $admin_role_id);
    }

    function create_tables($databaseConnection)
    {
        $query_users = "CREATE TABLE IF NOT EXISTS users (id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50), password CHAR(40), PRIMARY KEY (id))";
        $databaseConnection->query($query_users);

        $query_roles = "CREATE TABLE IF NOT EXISTS roles (id INT NOT NULL, name VARCHAR(50), PRIMARY KEY (id))";
        $databaseConnection->query($query_roles);

        $query_users_in_roles = "CREATE TABLE IF NOT EXISTS users_in_roles (id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, role_id INT NOT NULL, ";
        $query_users_in_roles .= " PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (role_id) REFERENCES roles(id))";
        $databaseConnection->query($query_users_in_roles);

        $query_pages = "CREATE TABLE IF NOT EXISTS pages (id INT NOT NULL AUTO_INCREMENT, menulabel VARCHAR(50), content TEXT, PRIMARY KEY (id))";
        $databaseConnection->query($query_pages);

        $query_events = "CREATE TABLE IF NOT EXISTS events(id INT NOT NULL AUTO_INCREMENT, event_id INT, name VARCHAR(255), PRIMARY KEY (id))";
        $databaseConnection->query($query_events);

        $query_meets = "CREATE TABLE IF NOT EXISTS meets (id INT NOT NULL AUTO_INCREMENT, meet_type INT NULL, date DATETIME NOT NULL, name VARCHAR(255) NOT NULL, ".
                       "results_file VARCHAR(63) NULL, uploader_id INT NULL, PRIMARY KEY (id))";
        $databaseConnection->query($query_meets);

        $query_meet_types = "CREATE TABLE IF NOT EXISTS meet_types(id INT NOT NULL, type_name VARCHAR(31) NOT NULL, PRIMARY KEY(id))";
        $databaseConnection->query($query_meet_types);

        $query_scores = "CREATE TABLE IF NOT EXISTS scores ( id INT NOT NULL AUTO_INCREMENT, swim_id INT NOT NULL, event_id INT NOT NULL, meet_id INT NOT NULL, ".
                        "seed_time VARCHAR(63), final_time VARCHAR(63), place VARCHAR(15), relay_team VARCHAR(15), relay_pos INT, sort_pos INT, ".
                        " PRIMARY KEY (id))";
        $databaseConnection->query($query_scores);

        $query_swimmers = "CREATE TABLE IF NOT EXISTS swimmers (id INT NOT NULL AUTO_INCREMENT, firstname VARCHAR(63), lastname VARCHAR(63), ".
                          "nickname VARCHAR(63) NOT NULL, team_id INT, PRIMARY KEY (id))";
         $databaseConnection->query($query_swimmers);

        $query_teams = "CREATE TABLE IF NOT EXISTS teams (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(63), location VARCHAR(63), PRIMARY KEY (id))";
        $databaseConnection->query($query_teams);
    }

    function create_roles($databaseConnection, $admin_role_id)
    {
        $query_check_roles_exist = "SELECT id FROM roles WHERE id <= 2";
        $statement_check_roles_exist = $databaseConnection->prepare($query_check_roles_exist);
        $statement_check_roles_exist->execute();
        $statement_check_roles_exist->store_result();
        if ($statement_check_roles_exist->num_rows == 0)
        {
            $query_insert_roles = "INSERT INTO roles (id, name) VALUES ($admin_role_id, 'admin'), (2, 'user')";
            $statement_inser_roles = $databaseConnection->prepare($query_insert_roles);
            $statement_inser_roles->execute();
        }
    }

    function create_admin($databaseConnection, $admin_role_id)
    {
        // HACK: Storing config values in variables so that they aren't passed by reference later.
        $default_admin_username = DEFAULT_ADMIN_USERNAME;
        $default_admin_password = DEFAULT_ADMIN_PASSWORD;

        $query_check_admin_exists = "SELECT id FROM users WHERE username = ? LIMIT 1";
        $statement_check_admin_exists = $databaseConnection->prepare($query_check_admin_exists);
        $statement_check_admin_exists->bind_param('s', $default_admin_username);
        $statement_check_admin_exists->execute();
        $statement_check_admin_exists->store_result();
        if($statement_check_admin_exists->num_rows == 0)
        {
            $query_insert_admin = "INSERT INTO users (username, password) VALUES (?, SHA(?))";
            $statement_insert_admin = $databaseConnection->prepare($query_insert_admin);
            $statement_insert_admin->bind_param('ss', $default_admin_username, $default_admin_password);
            $statement_insert_admin->execute();
            $statement_insert_admin->store_result();

            $admin_user_id = $statement_insert_admin->insert_id;
            $query_add_admin_to_role = "INSERT INTO users_in_roles(user_id, role_id) VALUES (?, ?)";
            $statement_add_admin_to_role = $databaseConnection->prepare($query_add_admin_to_role);
            $statement_add_admin_to_role->bind_param('dd', $admin_user_id, $admin_role_id);
            $statement_add_admin_to_role->execute();
            $statement_add_admin_to_role->close();
        }
    }

    function getEventNameByID($eventid)
    {
        global $databaseConnection;

        $query = "SELECT gender, age, length, style FROM events WHERE id = ? LIMIT 1";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('i', $eventid);
        $statement->execute();
        $statement->store_result();
        $statement->bind_result($eventgender, $eventage, $eventlength, $eventstyle);
        $statement->fetch();

        return "Event $eventid $eventgender $eventage $eventlength Yards $eventstyle";
    }

    define("TEAM_NAME", 0);
    define("FULL_NAME", 1);
    define("LOCATION", 2);

    function getTeamDetailsByID($teamid, $type = TEAM_NAME) {

        global $databaseConnection;
        $query = "SELECT location, name FROM teams WHERE id = ? LIMIT 1";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('i', $teamid);
        $statement->execute();
        $statement->store_result();
        $statement->bind_result($location, $name);
        if (is_null($ret = $statement->fetch()))
            return NULL;
        else if ($ret === FALSE)
            return FALSE;

        switch($type) {
            case FULL_NAME: return $name.' '.$location;
            case LOCATION: return $location;
            default:
            case TEAM_NAME: return $name;
        }
            
    }

    // returns swim id if found,
    // returns NULL if not found,
    // returns FALSE if error
    function lookupSwimIDByName($firstname, $lastname) {
        //mysqli_report(MYSQLI_REPORT_ALL);
        $firstname = trim($firstname);
        $lastname = trim($lastname);
        global $databaseConnection;
        $query = "SELECT id FROM swimmers WHERE firstname = ? AND lastname = ?";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('ss', $firstname, $lastname);
        $statement->execute();
        $statement->store_result();
        $statement->bind_result($id);

        if (is_null($ret = $statement->fetch()))
            return NULL;
        else if ($ret === FALSE)
            return FALSE;
        else   
            return $id;
    }

    function lookupTeamIDByName($teamstring)
    {
        //mysqli_report(MYSQLI_REPORT_ALL);
        global $databaseConnection;
        $query = "SELECT teams.id FROM teams WHERE LOCATE(teams.location, ?) != 0 ".
                 "OR LOCATE(teams.name, ?) != 0 LIMIT 1";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('ss', $teamstring, $teamstring);
        $statement->execute();
        $statement->store_result();
        $statement->bind_result($id);

        if (is_null($ret = $statement->fetch()))
            return NULL;
        else if ($ret === FALSE)
            return FALSE;
        else   
            return $id;
    }

    function lookupEventIDByName($name) 
    {
        global $databaseConnection;
        $name = '%'.$name.'%';
        $query = "SELECT event_id FROM events WHERE name LIKE ? LIMIT 1";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('s', $name);
        $statement->execute();
        $statement->store_result();
        $statement->bind_result($id);

        if (is_null($ret = $statement->fetch()))
            return NULL;
        else if ($ret === FALSE)
            return FALSE;
        else   
            return $id;
    }

    function lookupUserIDByName($name) {
        global $databaseConnection;
        $query = "SELECT id FROM users WHERE username LIKE ? LIMIT 1";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('s', $name);
        $statement->execute();
        $statement->store_result();
        $statement->bind_result($id);

        if (is_null($ret = $statement->fetch()))
            return NULL;
        else if ($ret === FALSE)
            return FALSE;
        else   
            return $id;   
    }

    function lookupMeetIDByDate($date)
    {
        $date = new DateTime($date);
        $date = $date->format('Y-m-d');

        global $databaseConnection;
        $query = "SELECT id FROM meets WHERE date = ?";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('s', $date);
        $statement->execute();
        $statement->store_result();
        $statement->bind_result($id);

        if (is_null($ret = $statement->fetch()))
            return NULL;
        else if ($ret === FALSE)
            return FALSE;
        else   
            return $id; 
    }

    function getNextNickNum()
    {
        $file = fopen(ROOT_DIR.'nicknum.config','r');
        $val = rand(3,7) + intval(fgets($file));
        fclose($file);

        $file = fopen(ROOT_DIR.'nicknum.config', 'w');
        fwrite($file, strval($val));
        fclose($file);

        return $val;
    }

    function insertNewUser($username, $password)
    {
        global $databaseConnection;
        $query = "INSERT INTO users (username, password) VALUES (?, SHA(?))";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('ss', $username, $password);
        $statement->execute();
        $statement->store_result();   
        
        return $databaseConnection->insert_id; 
    }

    // inserts a swimmer into the database 
    // creates nickname, random if flag is set to true; 
    function insertNewSwimmer($firstname, $lastname, $teamid, $randomnick = TRUE)
    {
        //create nickname:
        if ($randomnick)
            $nickname = 'Swimmer'.getNextNickNum();
        else 
            $nickname = $firstname.$lastname[0];

        global $databaseConnection;
        $query = "INSERT INTO swimmers (firstname, lastname, nickname, team_id) VALUES (?,?,?,?)";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('sssi', $firstname, $lastname, $nickname, $teamid);
        $statement->execute();
        $statement->store_result();
        return $databaseConnection->insert_id;
    }

    // inserts a team into the database
    // location is optional, it simply adds more search robustness
    function insertNewTeam($name, $location = NULL)
    {
        global $databaseConnection;
        if (isset($location)) {
            $query = "INSERT INTO teams (name, location) VALUES (?,?)";
            $statement = $databaseConnection->prepare($query);
            $statement->bind_param('ss', $name, $location);
        } else {
           $query = "INSERT INTO teams (name) VALUES (?)";
            $statement = $databaseConnection->prepare($query);
            $statement->bind_param('s', $name);
        }
        $statement->execute();
        $statement->store_result();
        return $databaseConnection->insert_id;
    }

    // inserts a single score into the database
    function insertScore($swimid, $eventid, $meetid, $seed, $final, $place, $sort_pos, $relay_team = NULL, $relay_place = NULL)
    {
        global $databaseConnection;
        if (isset($relay_team)) {
            $query = "INSERT INTO scores (swim_id, event_id, meet_id, seed_time, final_time, place, relay_team, relay_pos, sort_pos)". 
                     "VALUES (?,?,?,?,?,?,?,?,?)";
            $statement = $databaseConnection->prepare($query);
            $statement->bind_param('iiisssssi', $swimid, $eventid, $meetid, $seed, $final, $place, $relay_team, $relay_place, $sort_pos);
        } else {
           $query = "INSERT INTO scores (swim_id, event_id, meet_id, seed_time, final_time, place, sort_pos)".
                    "VALUES (?,?,?,?,?,?,?)";
            $statement = $databaseConnection->prepare($query);
            $statement->bind_param('iiisssi', $swimid, $eventid, $meetid, $seed, $final, $place, $sort_pos);
        }

        $statement->execute();
        $statement->store_result();
        return $databaseConnection->insert_id;
    }

    function insertMeet($meet_type, $name, $date, $filename, $uploader_id = 0)
    {
        // read and convert date
        $date = new DateTime($date);
        $date = $date->format('Y-m-d');
        $meet_type = intval($meet_type);

        global $databaseConnection;
        $query = "INSERT INTO meets (meet_type, name, date, results_file, uploader_id) VALUES (?, ?, ?, ?, ?)";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('isssi', $meet_type, $name, $date, $filename, $uploader_id);
        $statement->execute();
        $statement->store_result();
        return $databaseConnection->insert_id;
    }

    // adds a whole meet based on score arrays
    function addWholeMeet($meetdata, $uploader_id = 0) {
        global $databaseConnection;
        global $swimid_cache;
        global $teamid_cache;

        $scores = $meetdata['scores'];
        $metadata = $meetdata['metadata'];
        $date = $meetdata['date'];
        $meetid = insertMeet($metadata['type'], $metadata['name'], $date, 
                             $meetdata['filename'], $uploader_id);
        if ($meetid == 0) {
            echo "Error: Could not insert into meets table";
            return FALSE;
        }

        $databaseConnection->autocommit(FALSE);

        foreach ($scores as $score) {

            //look up name in swimid cache
            $swimid_key = $score['lastname'].','.$score['firstname'];
            if (isset($swimid_cache[$swimid_key])) {
                $swimid = $swimid_cache[$swimid_key];
            } else {
                // look up swimid by name, create swimmer if needed
                $swimid = lookupSwimIDByName($score['firstname'], $score['lastname']); 
                if (is_null($swimid) || $swimid === FALSE) {
                    if ($swimid === FALSE) echo ('Warning: Name lookup failed. Creating new swimmer');

                    // look up team id for swimmer
                    // if team can't be found, create placeholder team for subsequence matches
                    if (isset($teamid_cache[$score['team']])) {
                        $teamid = $teamid_cache[$score['team']];
                    } else {
                        $teamid = lookupTeamIDByName($score['team']);
                        if ($teamid === FALSE || is_null($teamid)) {
                            echo 'Warning: "'.$score['team'].'" Team lookup failed. Creating placeholder team, Swimmer:'.$score['firstname'].' '.$score['lastname'];
                            $teamid = insertNewTeam($score['team']);
                        }
                        $teamid_cache[$score['team']] = $teamid;
                    }

                    // create random nicks for any non Sea Otter teams
                    if ($teamid == 1) {
                        $randomnick = FALSE;
                    } else {   
                        $randomnick = TRUE;
                    }

                    $swimid = insertNewSwimmer($score['firstname'],$score['lastname'],$teamid, $randomnick);
                }

                // put in swimid cache:
                $swimid_cache[$swimid_key] = $swimid;
            }

            $eventid = intval($score['eventid']);
            $seed = '';
            $final = $score['final'];
            $place = $score['place'];
            $sort_pos = $score['sort_pos'];

            // add relays scores:
            if ($score['relay'])  {
                $relay_team = $score['relay_team'];
                $relay_pos = $score['relay_pos'];
                insertScore($swimid, $eventid, $meetid, $seed, $final, $place, $sort_pos, $relay_team, $relay_pos);
            } else {
                 insertScore($swimid, $eventid, $meetid, $seed, $final, $place, $sort_pos);  
            }
        }
        $databaseConnection->commit();
        $databaseConnection->autocommit(TRUE);
    }

    // deletes a whole meet with scores
    function deleteWholeMeet($meetid, $uploaderid)
    {
        global $databaseConnection;
        global $errors;

        if ($uploaderid == 0) {
            $query = "DELETE FROM meets WHERE id = ?";
            $statement = $databaseConnection->prepare($query);
            $statement->bind_param('i', $meetid);
        } else {
            $query = "DELETE FROM meets WHERE id = ? AND uploader_id = ?";
            $statement = $databaseConnection->prepare($query);
            $statement->bind_param('ii', $meetid , $uploaderid);
        }
        $statement->execute();
        $statement->store_result();

        if ($statement->affected_rows == 0) {
            addError('Error: Invalid meet');
            return FALSE;
        }

        $query = "DELETE FROM scores WHERE meet_id = ?";
        $statement = $databaseConnection->prepare($query);
        $statement->execute();
        $statement->store_result();

        return TRUE;
    }

    function clearTables() {
        //mysqli_report(MYSQLI_REPORT_ALL);

        global $databaseConnection;
        $query = "DELETE FROM swimmers WHERE id != 1";
        $statement = $databaseConnection->prepare($query);
        $statement->execute();
        $statement->store_result();

        $query = "DELETE FROM meets";
        $statement = $databaseConnection->prepare($query);
        $statement->execute();
        $statement->store_result();

        $query = "DELETE FROM scores";
        $statement = $databaseConnection->prepare($query);
        $statement->execute();
        $statement->store_result();
    }

    function getListOfCurrentSwimmers() {
        global $databaseConnection;
        $query = "SELECT s.id, s.nickname FROM swimmers s WHERE ".
                 "EXISTS(SELECT NULL FROM scores r, meets m WHERE ".
                 "s.id = r.swim_id AND m.id = r.meet_id AND m.date >= ?)";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('s', date('Y-01-01'));
        $statement->execute();
        $statement->store_result();

        $statement->bind_result($id, $nickname); 
        $curSwimmers = array();


        
    }

    function getListOfPastSwimmers() {
        
    }

    // returns a list of keyed arrays representing the meets
    function getListOfMeets()
    {
        global $databaseConnection;
        $query = "SELECT id, name, date FROM meets ORDER BY date ASC";
        $statement = $databaseConnection->prepare($query);
        $statement->execute();
        $statement->store_result();
        $statement->bind_result($id, $name, $date);

        $meets = array();
        while ($statement->fetch())
            array_push($meets, array(
                'id' => $id,
                'name' => $name,
                'date' => $date
            ));

        return $meets;
    }

    function getListOfMeetsByUploaderID($uploaderid) {
        global $databaseConnection;
        $query = "SELECT id, name, date FROM meets WHERE uploader_id = ? ORDER BY date ASC";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('i', $uploaderid);
        $statement->execute();
        $statement->store_result();
        $statement->bind_result($id, $name, $date);

        $meets = array();
        while ($statement->fetch())
            array_push($meets, array(
                'id' => $id,
                'name' => $name,
                'date' => $date
            ));

        return $meets;   
    }

    // returns of list of keyed arrays containing role data
    function getListOfRoles() {
        global $databaseConnection;
        $query = "SELECT id, name FROM roles ORDER BY id DESC";
        $statement = $databaseConnection->prepare($query);
        $statement->execute();
        $statement->store_result();
        $statement->bind_result($id, $name);

        $roles = array();
        while ($statement->fetch())
            array_push($roles, array(
                'id' => $id,
                'name' => $name,
            ));

        return $roles;
    }

    // helper function for updateSeedTimes
    function getSeedAndFinals($meetid) {
        global $databaseConnection;
        $meetid = intval($meetid);
        $query = "SELECT id, swim_id, event_id, seed_time, final_time FROM scores WHERE meet_id = ?";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('i', $meetid);
        $statement->execute();
        $statement->store_result();
        $statement->bind_result($scoreid, $swimid, $eventid, $seed, $final);

        $scores = array();
        while ($statement->fetch()){
            array_push($scores, array(
                'scoreid' => $scoreid,
                'swimid'=> $swimid,
                'eventid' => $eventid,
                'seed' => $seed,
                'final' => $final
            ));
        }
        return $scores;
    }

    // helper function for updateEventSeeds
    function updateSeedTime($scoreid, $seed) {
        global $databaseConnection;
        $scoreid = intval($scoreid);
        $query = "UPDATE scores SET seed_time=? WHERE id=?";
        $statement = $databaseConnection->prepare($query);
        $statement->bind_param('si', $seed, $scoreid);
        $statement->execute();
        $statement->store_result();
    }

    // helper function for updateEventSeeds
    function timeStrToHunds($timestring)
    {
        if (preg_match ('~([\d]{1,2}):([\d]{2})\.([\d]{2})~', $timestring, $match)) {
            $time = (int) 6000*$match[1] + 100*$match[2] + $match[3];
        } else if (preg_match('~([\d]{1,2})\.([\d]{2})~', $timestring, $match)){
            $time = (int) 100*$match[1] + $match[2];
        } else {
            return FALSE;
        }

        return $time;
    }

    // helper function for updateEventSeeds 
    function compareTimes($timeStr1, $timeStr2) {

        $time1 = timeStrToHunds($timeStr1);
        $time2 = timeStrToHunds($timeStr2);

        if ($time1 == FALSE || $time2 == FALSE)
            return FALSE;
        if ($time1 < $time2)
            return -1;
        if ($time1 == $time2)
            return 0;
        if ($time1 > $time2)
            return 1;

        return FALSE;
    }

    // sorts through the database and updates 
    function updateSeedTimes($resetAllSeeds = FALSE) {
        
        global $databaseConnection;
        $databaseConnection->autocommit(FALSE);

        $curSeeds = array();
        $meets = getListOfMeets();
        foreach ($meets as $meet) {
            $scores = getSeedAndFinals($meet['id']);
            foreach($scores as $score) {
                
                $final = $score['final'];
                $scoreid = $score['scoreid'];
                $scoreString =  $score['swimid'].'_'.$score['eventid'];

                // only update seed if seed is null, or we are
                // resetting all seeds
                if (!isset($score['seed']) || $resetAllSeeds) {

                    if (!isset($curSeeds[$scoreString])) { // if not seed time found
                        updateSeedTime($scoreid, 'NT');
                        if ($final != 'DQ' && $final != 'NS' && $final != ' ') { // only update seed time if time is valid
                            $curSeeds[$scoreString] = $final;
                        }
                    } else if ($final == 'DQ' || $final == 'NS' || $final == ' ') { // seed time found and final time is invalid
                        updateSeedTime($scoreid, $curSeeds[$scoreString]);   
                    } else { // seed time found and final time is valid
                        updateSeedTime($scoreid, $curSeeds[$scoreString]);
                        if ( compareTimes($final, $curSeeds[$scoreString]) < 0) {
                            $curSeeds[$scoreString] = $final;
                        }        
                    }
                }

                 $databaseConnection->commit();
            }
        }

        $databaseConnection->autocommit(TRUE);
    }
    
?>